/**
 * 
 */
package userService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import userEntity.UserEntity;



/**
 * @author Arimarajan
 *
 */
public class UserService {
	private Connection connection;
	private PreparedStatement statement;
	
	public UserService(){

		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//mouse shared
		} 
	}
	
	
	/**
	 * This method fetches the details of the user
	 * from user table
	 * @param phoneNumber
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet fetchUserDetails(String phoneNumber) throws SQLException{
		ResultSet rs = null;
		String query = "Select * from user where phoneNumber=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1,phoneNumber);
			rs = statement.executeQuery();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	
	public void transactionCommit() throws SQLException{
		if(connection!=null){
			try {
				connection.commit();
			} catch (SQLException e) {
				if(connection != null){
					System.out.println("transaction is rolled back");
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
						throw e1;
					}
				}
				e.printStackTrace();
				throw e;
			}
			finally{
				try {
					connection.setAutoCommit(true);
				}
				catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}

	public void transactionRollBack() throws SQLException{

		try {
			if(connection != null){
				connection.rollback();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}

		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}
	/**
	 * This method updates the user details in user table
	 * @param user
	 * @return 
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet updateUserDetails(UserEntity user) throws SQLException{
		System.out.println(" Before SQL execution");
		ResultSet rs = null;
		String updatequery = "update user set name= ? , emailId = ? , address = ? , pincode = ?, phoneNumber = ? where phoneNumber = ?;";
		System.out.println("After SQL execution");
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery);
			System.out.println("EmailID +" + user.getEmailID());
			statement.setString(1, user.getName());
			statement.setString(2, user.getEmailID());
			statement.setString(3, user.getAddress());
			statement.setString(4, user.getPincode());
			statement.setString(5, user.getPhoneNumber());
			statement.setString(6, user.getOldPhoneNumber());
			statement.executeUpdate();
			
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
		
	}
	
	/**
	 * This method fetches the current password of the user
	 * @param phoneNumber
	 * @param oldPwd
	 * @return
	 */
	public ResultSet fetchOldPassword(String phoneNumber){
		System.out.println("User Service Called and will execute SQL : ");
		ResultSet rs = null;
		String query = "select password from login where userPhoneNumber = ? ;";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, phoneNumber);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			e1.printStackTrace();
		}

		return rs;
	}
	
	
	/**
	 * This method updates the user pwd in Login table
	 * @param phoneNumber
	 * @param pwd
	 * @throws SQLException 
	 */
	public void changePwd(String phoneNumber, String pwd) throws SQLException{
		String query =  "UPDATE login SET password=? WHERE userPhoneNumber=?"; 
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1,pwd);
			statement.setString(2,phoneNumber);
			statement.executeUpdate();	
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}
	
	/**
	 * This method deactivates the user in the login table
	 * @param phoneNumber
	 * @throws SQLException 
	 */
	public void deActivate(String phoneNumber) throws SQLException{
		String query =  "UPDATE login SET active=0 WHERE userPhoneNumber=?"; 
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1,phoneNumber);
			statement.executeUpdate();	
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		
	}
	
	/**
	 * This method activates the user in the login table
	 * @param phoneNumber
	 * @throws SQLException 
	 */
	public void Activate(String phoneNumber) throws SQLException{
		String query =  "UPDATE login SET active=1 WHERE userPhoneNumber=?"; 
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1,phoneNumber);
			statement.executeUpdate();	
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		
	}
	
	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}
}
